# coding: utf-8
import pymysql.cursors
import time
import datetime

i = 1
sum = 0


class PdbcUtil():
    def __init__(self, host='localhost', db='mqtt'):
        self.conn = pymysql.connect(host=host, user='root', password='buaa1714', db='mqtt', charset='utf8', port=3306)
        self.cursor = self.conn.cursor()

    def insertData2(self, sensorId='hahah', RH=0.0, temp=0.0, light=0.0):
        currentTime = int(time.time())
        now = datetime.datetime.now()
        now = now.strftime("%Y-%m-%d %H:%M:%S")

        sql = "insert into data(createdAt,sensorId,RH,temp,light)values(\"%s\",\"%s\",%f,%f,%f);" % (
        now, sensorId, RH, temp, light)

        print(sql)
        self.cursor.execute(sql)
        self.conn.commit()

    def insertLog(self, topic, message, qos):
        currentTime = int(time.time())
        now = datetime.datetime.now()
        now = now.strftime("%Y-%m-%d %H:%M:%S")

        sql = "insert into mqtt(createdAt,topic,message,qos)values(\"%s\",\"%s\",\"%s\",NULL);" % (now, topic, message)
        print(sql)
        self.cursor.execute(sql)
        self.conn.commit()

    def insertRHAndTempData(self, temp: float, RH: float, CO2: float, TVOC: float, t=0):
        currentTime = 0
        if (t == 0):
            currentTime = int(time.time())
        else:
            currentTime = t
        sql = "insert into sample(samplingtime,temp,RH,CO2,TVOC)values(%d,%f,%f,%f,%f);" % (
            currentTime, temp, RH, CO2, TVOC)
        self.cursor.execute(sql)
        self.conn.commit()

    def insertEventData(self, eventType: str):
        currentTime = int(time.time())
        sql = "insert into events(eventtime,eventType)values(%d,\'%s\');" % (currentTime, eventType)
        self.cursor.execute(sql)
        self.conn.commit()

    def getUserToAlert(self, alertingSensor: str)->list:# 获取发送警报的的用户
        currentTime = int(time.time())
        sql = "select user.email from user,labs where user.labId=labs.labId and labs.sensorId='%s';" % (alertingSensor)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()

        self.conn.commit()
        print(result)
        l0=[]
        for tup in result:
            l0.append(tup[0])
        return l0


if __name__ == '__main__':
    pu = PdbcUtil()
    s=pu.getSampleData('mysensor')
    print(s)

